Tutorials and examples - How do I ... ?

Preface

This section enlists a set of answers on common How do I ... ? questions related to the generated code and the functionality LLBLGen Pro provides and a list of all the example code inside the various sections in this documentation. The How do I...? examples are given in C# and VB.NET and if required in both template group semantics, and if not, SelfServicing is used. All examples use the Northwind database schema, unless stated otherwise.

Examples in the documentation

Below you'll find an extensive list of all the example snippets located in this documentation. This list is to give you a quick access to code snippets to perform a well defined goal without having to read all the documentation first to locate the examples. It's recommended that you read the paragraph in which the example is located as well, to get more information about when the example is useful. The examples are stored in the order in which they appear in the documentation if you read everything from top to bottom.
Using the generated code, SelfServicing specific
Using the generated code, Adapter specific
Using the generated code, general
Using the generated code, Linq to LLBLGen Pro

How do I ... ?

How do I read all entities of a given type into a collection?

   Selfservicing

   Adapter

Back to the list
How do I insert a new entity?

   Selfservicing

   Adapter

Back to the list
How do I delete an existing entity?

Method 1: entity is already loaded into memory.

   Selfservicing

   Adapter

Method 2: use a new entity object to directly delete an entity from the database. Just set the PK fields to a value.

   Selfservicing

   Adapter

Method 3: use a predicate expression to delete the entity directly.

   Selfservicing

   Adapter

Back to the list
How do I pass different connection strings at runtime using Adapter?

All generated code (SelfServicing or Adapter) by default reads the connection string from the *.config file of the application referencing the generated code. You can pass a different connection string than the one generated into the app.config file to the DataAccessAdapter's class constructor. That instance will then use the connection string passed in instead of the connection string defined in the *.config file.

// C#
DataAccessAdapter adapter = new DataAccessAdapter("data source=myServer;initial catalog=Northwind;UID=myUserID;PWD=secret");

Back to the list
How do I test if an entity is actually loaded from the database?

   Selfservicing

   Adapter

note Note:
LLBLGen Pro won't change the catalog / schema based on the connectionstring you're specifying. The connection string is used to connect to the database system, though persistence information in the generated code contains the catalog name(s) to target. If you want to modify the catalog to target with the code at runtime, please specify name overwriting settings, as discussed in DataAccessAdapter functionality, for sqlserver and Application configuration through .config files

Back to the list
How can I determine at runtime which fields are the primary key fields of an entity?

References to these fields are stored in the ArrayList returned by the property PrimaryKeyFields of an entity object. You can also walk the field objects in the collection returned by the Fields property of an entity object and check the IsPrimaryKey.

Back to the list
How do I insert a NULL value for a field in a new entity?

When you create an entity object, all fields get a default value. When you save the entity (using the SelfServicing method or the Adapter method), all changed fields are saved, all non-changed fields are skipped. This means, that if you for example save a new CustomerEntity and you don't set the ContactTitle property to a value, it will not be included in the INSERT query, which will result in a NULL value for ContactTitle in the database table. If a default constraint would have been defined for that field, the RDBMS would have inserted that value instead of NULL.

Back to the list
How do I insert a NULL value for a field in an existing entity?

Because a save action of an existing entity will update the changed fields only, we can't use the mechanism used with saving a new entity. Instead we have to change the value of the field we want to set to NULL to a value, so it is changed, and at the same time the value should then be used to set the field to NULL. Because the properties reflecting the fields are typed, some with value types, we can't set the value typed properties to null/Nothing. Instead we use a method to do that.
Selfservicing / Adapter 
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
customer.SetNewFieldValue((int)CustomerFieldIndex.ContactTitle, null);
// now save the customer. ContactTitle will become NULL.

Instead of a field index, a field name can also be used: 
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
customer.SetNewFieldValue("ContactTitle", null);
// now save the customer. ContactTitle will become NULL.

Back to the list
How do I prevent that new entities are added to an entity collection in a grid?

The Adapter's EntityCollection class and the SelfServicing entity collection classes implement the IBindingList.AllowNew property. Setting this property to false/False will assure that there will be no 'new' line in the grid bound to the collection object and the grid is not able to add new entities via the IBindingList interface.

Back to the list
How do I enable sorting functionality of an entity collection in a bound grid?

The Adapter's EntityCollection class and the SelfServicing entity collection classes implement the IBindingList.SupportsSorting property and the IBindingList sorting functionality. Setting this property to true/True before the collection is bound to a grid, will make sure that the grid will be able to sort the contents of the collection when the user clicks a column header. Some grid controls offer sorting capabilities without this property, however grids based on .NET's own grid control require this property set to true to enable sorting capabilities.

Back to the list
How do I set a time-out setting of 1 minute using SelfServicing?

You can set a timeout (in seconds) for all commands executed using a global setting in the generated CommonDaoBase class. (HelperClasses namespace). It doesn't hurt to use a timeout larger than what you actually need, so setting it to 1 minute to make sure a long running query will succeed is not degrading performance in your application. If a connection to the database is not available, it will however then take longer to timeout. ADO.NET's default is 15 seconds, the default in the templates is set to 30 seconds. 
// C#
// set it to 60 seconds
CommonDaoBase.CommandTimeOut = 60;

Back to the list
How do I update a series of entities directly in the database?

The example below will update all EmployeeID fields in all orders with the value of 5 where the current EmployeeID field is 2.

   SelfServicing

   Adapter

Back to the list
How do I filter on fields in another entity?

Below is an example which retrieves all Customer entities which have bought a given product, in this case the product with ProductID 2

   SelfServicing

   Adapter

Back to the list
How do I handle exceptions which occur during save/delete/fetch actions?

Each exception caught during the execution of the SQL the Dynamic Query Engines generate is wrapped in a ORMQueryExecutionException exception object. You can thus create an extra catch clause which solely catches this exception. The ORMQueryExecutionException exception class embeds the caught exception as the inner exception, the complete SQL executed in text and the parameters collection passed to the query. This ensures you have all the information needed to fully debug the situation.

Back to the list
How do I implement concurrency control?

Concurrency control is something that can be implemented in various ways. Because there are numerous ways to implement concurrency control (abstract concurrency control using functionality locking, low level concurrency schemes with optimistic locking or pessimistic locking, all fields filters, timestamp filters etc.), the generated code offers you the tools to produce the concurrency scheme you want. To implement low level concurrency control like optimistic locking, predicates are used to limit the scope of a query executed. These predicates are added to the query being executed, for example an

To produce these predicates automatically, it's wise to implement IConcurrencyPredicateFactory for the class(es) you want concurrency control for. See the sections about concurrency control (Selfservicing or Adapter) for more details about this.

Back to the list
How do I create a m:n relation between two entity objects?

The generated code will not save intermediate table entries in an m:n relation. Consider the two entities Department (DepartmentID (PK, int, identity), Description) and Employee (EmployeeID (PK, int, identity), Name). These two entities have an m:n relation, and this results in the third entity DepartmentEmployees (DepartmentID (PK), EmployeeID (PK)). To relate a new employee with an existing department follow the example below. It will add a new employee to the existing department with ID 1. You can of course also set the DepartmentID of the DepartmentEmployeesEntity by hand, avoiding the necessity to fetch the DepartmentEntity first.

   SelfServicing

   Adapter

Back to the list
How do I fetch a typed view or typed list in a transaction using SelfServicing (SqlServer)?

It can be necessary to fetch a typed view or typed list during a transaction. Because in SqlServer, writers block readers, it is required that the fetch of the typed list or typed view is done using the same connection as the transaction uses. However typed lists and typed views are not addable to a Transaction object, like entities and entity collection classes are. Typed lists and typed views have a Fill() method overload which accepts a Transaction object. Use that overload to pass an existing transaction object and the Fill() method will use the connection of the passed in transaction to retrieve the data requested instead of using a new connection. This avoids deadlocks because the writers used in the transaction block a read (fetch) by the Fill() method.

Back to the list
How do I speed up manual bulk additions to entity collection objects?

If you manually add entities to an EntityCollection object (adapter) or EntitynameCollection (SelfServicing) object, you'll run into a performance penalty as the Add() method will check wether the entity added is already in the collection or not. This can lead to severe slowness in an application if the collection already holds a lot of entity objects (Add() uses List.Contains() which is a .NET method and which performs a linear search). To prevent this performance penalty, for example if you don't care if an entity object is added twice, you can set the EntityCollection (Adapter) or EntitynameCollection (SelfServicing) property DoNotPerformAddIfPresent to false. When you do that, all Add() calls will skip checking if an entity is already in the collection and will be significant faster. FetchEntityCollection() always sets this property to false before fetching new entities in the collection.

Back to the list
How do I sort the fields in a typed list?

Sorting a typed list is similar to sorting the objects in an entity collection. Say we have a typed list with the fields of 'Customer' and 'Order', and we want to sort the data on 'OrderDate' descending and 'CompanyName' ascending. First we define the sortexpression. This is the same for SelfServicing and Adapter.

Selfservicing / Adapter 
// C#
SortExpression sorter = 
	(OrderFields.OrderDate | SortOperator.Descending) & (CustomerFields.CompanyName | SortOperator.Ascending);

This sortexpression can now be passed as the sort expression in a typed list fetch. Below are the typed list fetch statements using SelfServicing and Adapter. Our typed list is called OrderCustomerTypedList.

   Selfservicing

   Adapter

Back to the list
How do I write a filter which does WHERE field = 3 ?

// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field == 3);

Back to the list
How do I write a filter which does WHERE fielda = fieldb ?

Field compares are done using a FieldCompareExpressionPredicate class. 
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(OrderFields.OrderDate == OrderFields.ShippingDate);

Back to the list
How do I write a filter which does WHERE field LIKE '%foo%' ?

// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field % "%Foo%");

Back to the list
How do I write a filter which does WHERE field BETWEEN 1 AND 10 ?

   SelfServicing

   Adapter

Back to the list
How do I write a filter which does WHERE field IN (1, 2, 5) ?

There are several ways to specify a range of fixed values: in an Array, in any IList implementing class like ArrayList or List<T> and directly in the call to the predicate creation. All three ways are shown below.

Using an array
// C#
IPredicateExpression filter = new PredicateExpression();
int[] values = new int[3] {1, 2, 5};
filter.Add(YourEntityFields.Field == values);

Using an IList implementing class, e.g. ArrayList
// C#
IPredicateExpression filter = new PredicateExpression();
ArrayList values = new ArrayList();
values.Add(1);
values.Add(2);
values.Add(5);
filter.Add(YourEntityFields.Field == values);

Using values directly in the call
   SelfServicing

   Adapter

Back to the list
How do I write a filter which does WHERE field IN (SELECT fieldb FROM foo) ?

   SelfServicing

   Adapter

Back to the list
How do I write a filter which does WHERE field IS NULL ?

// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field == DBNull.Value));

Back to the list
How do I use a group by clause in a dynamic list ?

The following query formulates a dynamic list with all managers and the number of employees which are reporting to them. The query is ran on Northwind, which contains a self-referencing Employees table.

   SelfServicing

   Adapter

Back to the list
How do I sort a filled entity collection ?

Sorting an entity collection client-side (i.e.: outside the database), can be done using the Sort() method implemented in the entity collections. There are various overloads. Two are shown below: sorting by specifying a property name and one using an entity field index and a custom comparer. The sorting is done on a filled collection of CustomerEntity objects, called customers.

Sorting by specifying a property name
The property name can be any property, also a property you've added in code, it doesn't have to be an entity field.

   SelfServicing

   Adapter

Back to the list
How do I get the MAX(Order.ShippingDate - Order.OrderDate) value from a customer's orders ?

   SelfServicing

   Adapter

Back to the list
How do I construct a multi-node prefetch path and specify sorting for one node ?

   SelfServicing

   Adapter

Another way to add the Orders and the Shippers nodes is the following. This example allows you to add more nodes to the Orders node.

   Selfservicing

   Adapter

Back to the list
How do I add an entity A to an entity B's collection of A's if A and B have an m:n relation ?

Say you want to add an Employee to a Department and Employee and Department have an m:n relation. This means that there is an intermediate entity DepartmentEmployees which defines the m:n relation with a foreign key to both Employee and Department. Employee has a collection Departments which results in all Department entities the Employee works for and Department has a collection Employees which results in all Employees working for the Department. In LLBLGen Pro, m:n relations are read-only, therefore, to add an Employee to a Department, you've to use the following steps. The example uses department and employee which are filled in DepartmentEntity and EmployeeEntity instances. You can also use new entities, this doesn't matter.

   Selfservicing

   Adapter

Keep in mind that the m:n relation is then saved in the database, but the situation in memory isn't updated, i.e.: employee.Departments doesn't contain 'department'. This is by design, as the m:n relation is based on a 3rd entity, which can be altered by any thread in the system, making the m:n relation, which is a result of the values of the intermediate entity, a 'view' of a situation at a given point in time.
Back to the list
LLBLGen Pro v3.0 documentation. ©2010 Solutions Design